const { getPool } = require('../../config/db')

class TemplatesCategoryModel {
  // 创建分类
  static async create({ name, parentId = 0, sortOrder = 0 }) {
    const pool = await getPool()
    const conn = await pool.getConnection()
    try {
      await conn.beginTransaction()

      // 验证父级分类
      const parent = await this.findById(parentId, conn)
      if (parentId !== 0 && !parent) {
        throw new Error('PARENT_NOT_FOUND')
      }

      // 计算层级
      const currentLevel = parent ? parent.level + 1 : 1
      if (currentLevel > 3) throw new Error('MAX_LEVEL_EXCEEDED')

      // 插入新分类
      const [insertResult] = await conn.query(
        `INSERT INTO flexipage_template_category 
         (name, parent_id, level, sort_order) 
         VALUES (?, ?, ?, ?)`,
        [name, parentId, currentLevel, sortOrder]
      )

      // 生成path
      const newPath = parent ? `${parent.path}${insertResult.insertId}/` : `${insertResult.insertId}/`
      await conn.query(
        `UPDATE flexipage_template_category 
         SET path = ? 
         WHERE id = ?`,
        [newPath, insertResult.insertId]
      )

      await conn.commit()
      return { id: insertResult.insertId, path: newPath }
    } catch (error) {
      await conn.rollback()
      throw error
    } finally {
      conn.release()
    }
  }

  // 根据ID查找分类
  static async findById(id, conn = getPool()) {
    const [rows] = await conn.query(
      `SELECT * FROM flexipage_template_category 
       WHERE id = ? AND is_deleted = 0`,
      [id]
    )
    return rows[0] || null
  }

  // 获取全量分类数据
  static async findAll() {
    const pool = await getPool()
    const [rows] = await pool.query(
      `SELECT id, name, parent_id, level, path 
       FROM flexipage_template_category 
       WHERE is_deleted = 0 
       ORDER BY path`
    )
    return rows
  }

  // 软删除分类及子类
  static async deleteWithChildren(id) {
    const pool = await getPool()
    const category = await this.findById(id)
    if (!category) throw new Error('CATEGORY_NOT_FOUND')

    await pool.query(
      `UPDATE flexipage_template_category 
       SET is_deleted = 1 
       WHERE path LIKE ?`,
      [`${category.path}%`]
    )
    return true
  }
}

module.exports = TemplatesCategoryModel